#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# 使用说明：
# 1、模板文件：origin.xlsx， 文件必需放在此文件上一级目录下。输出文件名：tmp.xlsx
# 2、模板文件格式：
# 3、随机报价配置，详见代码底部

# 模板文件
origin_file = "origin.xlsx"

# 输出文件
export_file = "tmp.xlsx"


import os

try:
    import pandas
except:
    os.system("pip install pandas")
    import pandas

try:
    import copy
except:
    os.system("pip install copy")
    import copy

try:
    import openpyxl
except:
    os.system("pip install openpyxl")
    import openpyxl
from openpyxl.utils import get_column_letter

try:
    import random
except:
    os.system("pip install random")
    import random




class BaoXiaoConfig:
    # 金额尾数
    prizeTailList = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
    # 金额涨幅随机数范围
    upRateFrom = 0
    upRateTo = 0

    def randomUpRate(self):
        if self.upRateFrom == 0 and self.upRateTo == 0:
            return 0
        else:
            return random.uniform(self.upRateFrom, self.upRateTo)

def sheet(rateFrom, rateTo, tailList = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]):
    config = BaoXiaoConfig()
    config.upRateFrom = rateFrom
    config.upRateTo = rateTo
    config.prizeTailList = tailList
    return config


def randomPrize(prize, upRate, prizeTailList):
    if upRate == 0:
        return prize
    if prize < 1000:
        newPrize = round(prize * (1 + upRate) / 10) * 10 + random.choice(prizeTailList)
    else:
        newPrize = round(prize * (1 + upRate) / 100) * 100
    return newPrize


def generate_sheet(
    source_file_path,
    source_sheet_name,
    target_file_path,
    target_sheet_name,
    config,
):
    # 加载源工作簿
    source_workbook = openpyxl.load_workbook(source_file_path)
    # 获取源工作表
    source_sheet = source_workbook[source_sheet_name]
    # 加载目标工作簿，如果不存在则创建一个新的
    try:
        target_workbook = openpyxl.load_workbook(target_file_path)
    except FileNotFoundError:
        target_workbook = openpyxl.Workbook()
        del target_workbook[target_workbook.active.title]

    # 创建一个新的工作表，名称为目标工作表名称
    target_sheet = target_workbook.create_sheet(title=target_sheet_name)
    # 复制单元格的值和格式

    for i in range(1, source_sheet.max_column + 1):
        target_sheet.column_dimensions[get_column_letter(i)].width = (
            source_sheet.column_dimensions[get_column_letter(i)].width
        )

    mergeFrom = None
    mergeTo = None
    for row in source_sheet.rows:
        if mergeTo != None and mergeFrom != None:
            target_sheet.merge_cells(range_string=mergeFrom + ":" + mergeTo)
        mergeFrom = None
        mergeTo = None
        isSumRow = False
        for cell in row:
            if type(cell).__name__ == "MergedCell":
                mergeTo = cell.coordinate
            else:
                if cell.column_letter == "A" and cell.value == "合计":
                    isSumRow = True
                if mergeTo != None and mergeFrom != None:
                    target_sheet.merge_cells(range_string=mergeFrom + ":" + mergeTo)
                    mergeFrom = None
                    mergeTo = None
                mergeFrom = cell.coordinate
                target_cell = target_sheet[cell.coordinate]
                if cell.column_letter == "B" and type(cell.value) == int:
                    target_cell.value = randomPrize(
                        cell.value,
                        config.randomUpRate(),
                        config.prizeTailList,
                    )
                elif cell.column_letter == "D" and type(cell.value) == int:
                    if isSumRow:
                        target_cell.value = "=SUM(D3:D" + str(cell.row - 1) + ")"
                    else:
                        target_cell.value = (
                            "=B" + str(target_cell.row) + "*C" + str(target_cell.row)
                        )
                else:
                    target_cell.value = cell.value
                target_cell.font = copy.copy(cell.font)
                target_cell.border = copy.copy(cell.border)
                target_cell.fill = copy.copy(cell.fill)
                target_cell.alignment = copy.copy(cell.alignment)

    # 保存目标工作簿
    target_workbook.save(target_file_path)
    # if 'Sheet' in target_workbook.sheetnames:
    #     target_workbook.remove('Sheet')

def run(configList):
    current_directory = os.path.dirname(os.path.abspath(__file__))
    filePath = os.path.abspath(os.path.join(current_directory, os.path.pardir)) + "/"

    originFile = filePath + origin_file
    exportFile = filePath + export_file

    if os.path.exists(exportFile):
        os.remove(exportFile)

    allData = pandas.read_excel(originFile, sheet_name=None, engine="openpyxl")
    originSheetName = list(allData.keys())[0]
    for config in configList:
        generate_sheet(originFile, originSheetName, exportFile, "0", config)


# 配置如下：
# 每一行表示每个sheet的配置。
# 例如：sheet(0.1, 0.2, [0, 5, 8]) 金额涨幅：在10%到20%之间随机。金额尾数为0，5，8随机
run([
    sheet(0, 0),
    sheet(0.1, 0.2),
    sheet(0.05, 0.1),
    sheet(0.01, 0.05),
    sheet(0.1, 0.2, [0, 5, 8]),
])